从 MySQL 转换为 PostgreSQL

我做数据可视化工作其实是从地理信息的可视化开始的，地理信息可视化的第一步，可能就是将数据库从 MySQL 转换为 PostgreSQL

安装 PostgreSQL

可参考 How To Install and Use PostgreSQL on CentOS 7

首先，我们不从 CentOS-Base 的源安装 PostgreSQL，以避免一些不及时更新的依赖包的问题，修改一下 CentOS-Base.repo 文件

$ sudo vi /etc/yum.repos.d/CentOS-Base.repo

在 [base] 和 [updates] 两个部分添加 exclude=postgresql* 如下：

...
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=\$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=\$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

exclude=postgresql*
...

然后我们安装 PostgreSQL 官网的 rpm

$ sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

之后你可以看到所有可安装的相关包列表：

$ yum list postgresql*

选择合适的版本进行安装（以版本13为例）：

$ sudo yum install postgresql13-server

PostgreSQL 的初始化

设置 PostgreSQL 的初始化和自启动

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
$ sudo systemctl start postgresql-13
$ sudo systemctl enable postgresql-13

检查一下：

$ ps -ef | grep postgres

应有类似如下的输出：

Output
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service.

初始化之后，PostgreSQL 会在 CentOS 系统中创建一个名为 postgres 的 Linux 用户，也会在当前数据库中建立一个名为 postgres 的同名 user 和一个名为 Postgres 的 role

角色(role)与用户(user)的关系

在PostgreSQL中，存在两个容易混淆的概念：角色/用户。对于PostgreSQL来说，在创建的时候： + CREATE ROLE freeoa PASSWORD 'freeoa'; 创建了角色 freeoa + CREATE USER freeoa2 PASSWORD 'freeoa2'; 创建了用户 freeoa2

它们有如下区别： + 用户默认有 login 权限，角色没有 + 角色和用户都可以关联着数据库权限，但更好的做法是将权限赋予角色，然后将角色关联给不同的用户 + 同一个角色可以关联不同的用户，同一个用户也可以关联多种角色

简单来说，角色（role）和 用户（user）没有本质上的区别，只有逻辑上的区别，用户更接近于关联真实的使用者，角色更接近于虚拟的形象，关联着不同数据库权限

所以，你可以切换到 postgres 直接登录数据库

$ su postgres
$ psql

进入数据库之后，一些基础的操作：

postgres=# \l                        查看所有数据库
postgres=# \q                        离开数据库
postgres=# \du                       列出所有用户
postgres=# \c [database_name]        连接具体数据库
postgres=# \d                        列出当前数据库的所有表格
postgres=# \d [table_name]           列出某一张表格的结构

当务之急是给 postgres 用户设置一个密码：

postgres=# ALTER USER postgres WITH PASSWORD 'NewPassword';

如果你不想使用默认的 postgres 用户，也可以创建新的用户（--interactive 表示会在创建过程中寻问是否设为 superuser）：

$ sudo -u postgres createuser --interactive

会有如下输出，需要确认一些 choices

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n)

这时，我们还只能使用 Linux 中的同名用户（比如postgres）来进行数据库登录，如果想其他用户通过 progres 用户名及密码完成登录如下：

$ psql -U postgres

还需修改配置文件 /var/lib/pgsql/13/data/pg_hba.conf

$ vi /var/lib/pgsql/13/data/pg_hba.conf

# 找到 local all all peer 一行，修改为下面
local all all md5

在 PostgreSQL 中创建数据库

创建数据库

postgres=# CREATE DATABASE mydb;

查看所有数据库

postgres=# \l

切换当前数据库

postgres=# \c mydb

创建表

postgres=# CREATE TABLE test(id int,body varchar(100));

查看当前数据库下所有表

postgres=# \d

赋予指定账户指定数据库所有权限

postgres=# GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

移除指定账户指定数据库所有权限

postgres=# REVOKE ALL PRIVILEGES ON DATABASE mydb TO test;

创建表

postgres=# CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);

一个创建表的实际例子

postgres=# CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);

上面所有命令需要注意尾部的 ; ，如果忘记，PostgreSQL 不会报任何错，但也不会执行任何效果。其他增删改查语句与标准 SQL 基本大差不差了，不多赘述了

添加远程访问

如果需要的话，可以开放远程端口

$ sudo firewall-cmd --add-port=5432/tcp --permanent
$ sudo firewall-cmd --reload

修改IP绑定

# 修改配置文件
$ vi /var/lib/pgsql/13/data/postgresql.conf

# 将监听地址修改为*
# 默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

允许所有IP访问

# 修改配置文件
$ vi /var/lib/pgsql/13/data/pg_hba.conf

# 在文件尾部加入
host all all 0.0.0.0/0 md5

重启PostgreSQL服务

# 重启PostgreSQL服务
$ sudo systemctl restart postgresql-13

配置完成后即可使用客户端进行连接

Python 与 Django 连接 PostgreSQL

可参考：Python 连接 postgresql 数据库

Python 下有一个连接 postgresql 数据库的包，叫 psycopg，我用的是 Django 4.2，支持 psycopg 3.1.8+ 或 psycopg2 2.8.4，推荐 psycopg 3.1.8+

$ pip install --upgrade pip
$ pip install psycopg

注意，并没有 psycopg3 这个包，只有 psycopg，目前最新的版本是 psycopg 3.2.0 dev1，但是 pypi.org 上只有 psycopg 3.1.12

基础使用，下面的代码来自 postgresql 官方文档：

# Note: the module name is psycopg, not psycopg3
import psycopg
 
# Connect to an existing database
with psycopg.connect("dbname=test user=postgres") as conn:
 
     # Open a cursor to perform database operations
    with conn.cursor() as cur:
 
         # Execute a command: this creates a new table
        cur.execute("""
            CREATE TABLE test (
                id serial PRIMARY KEY,
                num integer,
                data text)
            """)
 
        # Pass data to fill a query placeholders and let Psycopg perform
        # the correct conversion (no SQL injections!)
        cur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))

        # Query the database and obtain data as Python objects.
        cur.execute("SELECT * FROM test")
        cur.fetchone()
        # will return (1, 100, "abc'def")

        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
        # of several records, or even iterate on the cursor
        for record in cur:
            print(record)

        # Make the changes to the database persistent
        conn.commit()

在实际操作中，经常将数据库操作封装起来，作为一个类对外服务

class DBHandler:
  """
  Database handler
  """
  def __init__(self) -> None:
    self._conn = psycopg.connect("dbname=testdb user=someone password=123456")

  def addtagname(self, tid: int, tname: str):
    with self._conn.cursor() as cur:
      query = f"INSERT INTO tag_name_table (tid, tname) VALUES ({tid}, '{tname}')"
      cur.execute(query)
      self._conn.commit()

  def selecttag(self):
    pass

  def __del__(self):
    self._conn.close()

将 Mysql 数据导入到 PostgreSQL

最关键的一步到了，参考 Migrating a Django app from MySQL to PostgreSQL 和 Django Migrate MySQL to Postgres

备份 MySQL

$ mysqldump -u root -p dbname > out.sql

在 PostgreSQL准备新的数据库

创建对应的数据库

postgres=# CREATE DATABASE dbname OWNER rolename;

在 Django 的 settings.py 中增加数据库

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'dbname',
        'USER': 'dbuser',
        'PASSWORD': 'dbpass',
        'HOST': 'mysql.example.com',
        'PORT': '',
    },
    'postgresql': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'dbname',
        'USER': 'dbuser',
        'PASSWORD': 'dbpass',
        'HOST': 'postgresql.example.com',
        'PORT': '',
    }
}

通过 migrate 生成新的、空的 table

$ python manage.py migrate --database=postgresql

这里有一个小坑是，如果你的 Django 版本低于4.2，那你不能用 psycopg 3.1.12，Django还是会去找 psycopg2。一句话，要么升级 Django 至少到4.2，要么降级安装 psycopg2。（升级Django 4.2注意升级配套的包，比如MariaDB要在10.4以上，mysqlclient要在2.2以上等）

清洗新生成的数据库

有些 migrations 会生成一些 initial 的数据，所以我们要对新生成的数据表做一次清洗，以免在我们后面往里传数据时产生冲突。可以直接在数据库手动操作，也可以用 python manage.py sqlflush --database=postgresql 这个命令查看 Django 给出的建议语句

导入少量数据使用 dumpdata 和 loaddata

如果数据量不大的话，可以使用 Django 提供的 dumpdata 和 loaddata 来完成数据的导入工作，导出数据库：

$ python manage.py dumpdata --all --natural-foreign --output dump.json

一定要加 --natural-foreign 参数，否则外键关系无法导入新的数据库。可以加上 --verbosity 1 参数让过程可视化

导入数据库：

$ python manage.py loaddata dump.json --database=postgresql

如果数据量很少的话，使用 dumpdata 和 loaddata 是可以的。但这个命令本身不是为导入大量数据而设计的，所有数据在导入过程中都会存入内存，数据量比较多的话内存肯定会爆掉，而无法导出数据

可以参考 Migrating Django from MySQL to PostgreSQL the Easy Way 这篇文章来解决这个问题。当然，更好的方式是使用 pgloader

导入大量数据使用 pgloader

pgloader 是 Postgresql 的作者编写的一个工具，专门用来从各种数据来源导入数据到 Postgresql

pgloader 的 RPM 源包含在 yum.postgresql.org 当中，我们之前配置好了 postgresql 的安装，所以可以直接 yum 安装 pgloader

$ sudo yum install pgloader

查看安装成功：

$ pgloader --version

如果想从源码安装 pgloader 可参考：pgloader的安装与使用 和 使用PGLoader将MySQL数据库迁移到PostgreSQL

安装好 pgloader 之后，我们可以编写一个脚本 pgload.load 来告诉 pgloader 我们要从哪个数据库迁移数据到哪个数据库，以及需要额外操作些什么：

$ vim pgload.load

LOAD DATABASE
 FROM mysql://root:password@localhost:3306/temp_db
 INTO pgsql://postgres:password@localhost:5432/target_db

 WITH include drop, create tables, create indexes, workers = 8, concurrency = 1

 CAST type datetime to timestamp drop default drop not null using zero-dates-to-null

ALTER SCHEMA 'temp_db' RENAME TO 'public'
;

• LOAD DATABASE 指定从数据库加载
• FROM 源数据库
• INTO 目标数据库
• WITH 指定 PGLoader 行为
• include drop，迁移过程中，PGLoader 会删除目标 PostgreSQL 数据库中在源数据库中同名的任何表，注意备份
• create tables, 配置 PGLoader 根据源数据库数据在目标数据库中创建新表，如果使用 create no - - tables，则需要手动在目标数据库中创建好对应的表
• CAST type 类型转换匹配
• ALTER SCHEMA, 在 WITH 语句之后，配置特定 SQL 来告诉 PGLoader 执行其他操作

这里面有一个小坑是：假设 mysql 中有一个字段的类型为 datetime，转换到 PostgreSQL 为 timestamp with time zone，它变成带时区的时间了。此时pgloader并不会匹配上。所以脚本中必须要写这一句类型转换：**CAST type datetime to timestamp drop default drop not null using zero-dates-to-null**

执行 pgloader 语句

$ pgloader pgload.load

这时 pgloader 就会帮你完成数据的转换了

这里还有一个小坑是 pgloader 只支持 md5，但是我们新的 postgresql 默认的加密算法是 scram-sha-256，这会导致报错：

KABOOM!
FATAL error: Failed to connect to pgsql at "10.10.125.200" (port 5432) as user "postgres": 10 fell through ECASE expression. Wanted one of (0 2 3 4 5 6 7 8).
...

可通过如下方法进行规避：

1. Edit postgresql.conf, find the line password_encryption = scram-sha-256, change it to password_encryption = md5.
2. Edit pg_hba.conf, change all the scram-sha-256 to md5 for the listening address.
3. Restart the postgresql server.
4. Launch psql, change the password for the user I used for import \password import_user.
5. Run pgloader with the new password.
6. The database should be imported now.
7. Change password_encryption back to scram-sha-256 and authentication method back to scram-sha-256 in pg_hba.conf and restart the database server.
8. Reset the password again.

只能说，期待 pgloader 早日支持 scram-sha-256 吧！

其他需要注意的细节

Django signals

有时候我们会在 Django 项目中使用，当新记录发生时，会给 Users 发送 signals 比如 email 之类。在转换数据库，加载新数据之前如果设置了信号系统，应该避免触发起发生，比如使用 decorator

Constraints (such as not null, unique, foreign key)

各种约束都应该小心检查，在 dumpdata 时未加 --natural-foreign 会导致外键丢失；非空约束和唯一约束都应在数据导入之后做一次检查；格式转换比如 datetime to timestamp 都需要在导入数据时做好处理

Raw SQL queries

一些直接操作数据库的 query 在面对 Mysql 和 Postgresql 的写法是不同的，如果程序中有这种 raw 操作的话需要检查并作出修改

Case sensitivity

Mysql 的大小写敏感时由参数控制的：lower_case_file_system 和 lower_case_table_names，所以有些系统和设置中可能会被设置成大小写不敏感。Postgresql 是大小写敏感的